﻿CREATE PROCEDURE [dbo].[Hogar_Actividad_ObtenerInscripcionTalleres] 
 @IntegranteId INT 
AS 
BEGIN

/*****************************************************************************************************************************************/
-- Author: Carlos Mendoza
-- Purpose:   obtener todas las inscripciones a talleres de un integrante 
-- Returns:    Inscripcion
/*****************************************************************************************************************************************/ 
	
		SELECT [Nombre] AS [Actividad], 
			   ISNULL(i.[IntegranteId], @IntegranteId) AS [IntegranteId], 
			   a.[ActividadId], 
			   CASE WHEN YEAR(i.[FechaInscripcion]) = 1900 THEN NULL ELSE i.[FechaInscripcion] END AS [FechaInscripcion],
			   CASE WHEN ISNULL(i.[ActividadId], 0) = 0 THEN 0 ELSE i.[Activa] END AS [Activa],
			   i.[FechaBaja]
		FROM [dbo].[Actividad] a WITH (NOLOCK)
		LEFT JOIN (
			    SELECT [Seq], [IntegranteId], [ActividadId], [FechaInscripcion], [Activa], [FechaBaja]
				FROM
				(
					SELECT ROW_NUMBER() OVER(PARTITION BY [ActividadId] ORDER BY [FechaInscripcion] DESC, [Activa] DESC) AS [Seq],
						[IntegranteId], [ActividadId], [FechaInscripcion], [Activa], [FechaBaja]
					FROM [dbo].[Inscripcion]  WITH (NOLOCK)
					WHERE [IntegranteId] = @IntegranteId
				) t
				WHERE Seq=1	) i	 ON a.[ActividadId] = i.[ActividadId] AND i.[IntegranteId] = @IntegranteId
		WHERE a.[TipoActividadId] = 3 and a.[Activa] = 1
		ORDER BY [Nombre]
	
END